package com.bdqn.BookManage.dao.impl;

import com.bdqn.BookManage.dao.BookInfoDao;
import com.bdqn.BookManage.po.BookInfo;
import com.bdqn.BookManage.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * @author : CP
 * @version: 1.0
 * @program : BookManage
 * @description : 图书信息接口实现类
 * @date : 2020-07-16 16:48
 **/
public class BookInfoDaoImpl implements BookInfoDao {
    /**
     * 删除图片信息
     * @param id 图书编号
     * @return
     */
    @Override
    public boolean removeBookInfo(Integer id) {
        String sql = "delete from bookinfo where id = ?";
        Object [] obj = {id};

        return DBUtil.connZsg(sql,obj);
    }

    /**
     * 分页查询所有图书信息
     * 一行2列
     * @param id 页码
     * @return List<BookInfo>
     */
    @Override
    public List<BookInfo> queryAll(Integer id) {
        List<BookInfo> list = null;
        String sql = "select * from bookinfo limit ?,2";
        Object [] obj = {((id-1)*2)};
        list = (List<BookInfo>) DBUtil.queryAll(sql,obj,BookInfo.class);
        return list;
    }

    /**
     * 无条件查询所有图书的总记录数
     * @return int
     */
    @Override
    public int queryCount() {
        int i = 0;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Connection conn = DBUtil.open();

        String sql = "select count(*) from bookinfo";

        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            if(rs.next()){
                i = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(conn,ps,rs);
        }
        return i;
    }

    /**
     * 带条件查询所有图书的总记录数
     * @param name 图书名称
     * @return int
     */
    @Override
    public int queryCount(String name) {
        int i = 0;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Connection conn = DBUtil.open();

        String sql = "select count(*) from bookinfo where bookName like ?";

        try {
            ps = conn.prepareStatement(sql);
            ps.setObject(1,"%"+name+"%");
            rs = ps.executeQuery();
            if(rs.next()){
                i = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(conn,ps,rs);
        }
        return i;
    }

    /**
     * 新增图书信息
     * @param bookInfo 图书类
     * @return boolean
     */
    @Override
    public boolean newBook(BookInfo bookInfo) {
        String sql = "INSERT INTO `bookinfo` VALUES (null,?,?,?,?,?,?,?)";
        Object [] obj = {bookInfo.getBookName(),bookInfo.getPrice(),bookInfo.getTypeId(),
        bookInfo.getBornDate(),bookInfo.getImgUrl(),bookInfo.getState(),bookInfo.getStock()};
        return DBUtil.connZsg(sql,obj);
    }

    /**
     * 根据图书编号查询图书信息
     * @param bookId 图书编号
     * @return BookInfo
     */
    @Override
    public BookInfo querBookInfo(Integer bookId) {
        String sql = "select * from bookinfo where id = ?";
        Object [] obj = {bookId};
        List<BookInfo> list = (List<BookInfo>) DBUtil.queryAll(sql, obj, BookInfo.class);
        BookInfo bookInfo = list.get(0);
        return bookInfo;
    }

    /**
     * 根据图书编号修改图书信息
     * @param bookInfo 图书信息实体类
     * @return boolean
     */
    @Override
    public boolean updateById(BookInfo bookInfo) {
        String sql = "update bookinfo set bookName=?,price=?,typeId=?,bornDate=?,imgUrl=?,state=?,stock=? where id = ?";
        Object [] obj = {bookInfo.getBookName(),bookInfo.getPrice(),bookInfo.getTypeId(),bookInfo.getBornDate(),
        bookInfo.getImgUrl(),bookInfo.getState(),bookInfo.getStock(),bookInfo.getId()};

        return DBUtil.connZsg(sql,obj);
    }

    /**
     * 根据图书名称模糊查询并分页
     * @param bookName 图片名称
     * @param id 页码
     * @return List<BookInfo>
     */
    @Override
    public List<BookInfo> fuzzyQueryByBookName(String bookName, Integer id) {
        String sql = "select * from bookinfo where bookName like ? LIMIT ?,2";
        Object [] obj = {"%"+bookName+"%",(id-1)*2};
        List<BookInfo> list = (List<BookInfo>) DBUtil.queryAll(sql,obj,BookInfo.class);
        return list;
    }
}
